# 数据层 货物相关信息加载

# 货物信息
class Cargo:
    def __init__(self,carg_no,carg_price,carg_manufacturer,carg_stocknum,carg_minStocknum,carg_photo,carg_desc,carg_delStatus):
        self.no=carg_no                     # 1货物号
        self.price=carg_price               # 2价格
        self.manufacturer=carg_manufacturer # 3制造厂商
        self.stocknum=carg_stocknum         # 4储存量
        self.minStocknum=carg_minStocknum   # 5最小储存量
        self.photo=carg_photo               # 6货物图片
        self.desc=carg_desc                 # 7货物描述
        self.delStatus=carg_delStatus       # 8货物删除标识 1表示已删除，0表示未删除

    def toString(self):
        return "{} {} {} {} {} {} {} {}".format(self.no,self.price,self.manufacturer,self.stocknum,self.minStocknum,self.photo,self.desc,self.delStatus)


# 货物信息改变记录
class CargoAlter:
    def __init__(self,calt_no,calt_wai_no,calt_carg_no,calt_num,calt_price,calt_desc,calt_explian):
        self.no=calt_no             # 1记录号
        self.wai_no=calt_wai_no     # 2对应的客服员号
        self.carg_no=calt_carg_no   # 3对应的货物号
        self.num=calt_num           # 4改变之后的数量
        self.price=calt_price       # 5改变之后的价格
        self.desc=calt_desc         # 6改变之后的描述
        self.explain=calt_explian   # 7此记录的说明

    def toString(self):
        return "{} {} {} {} {} {}".format(self.no,self.wai_no,self.carg_no,self.num,self.price,self.desc)



## CargoAlter的操作
# 新增记录
def addCargoAlter(conn,cur,wai_no,carg_no,num,price,desc,explain):
    # 获取自增1的记录号
    sqlText = 'select * from cargoalter;'
    cur.execute(sqlText)
    result = cur.fetchall()
    no = len(result) + 1
    no_str=str(no).zfill(5)
    # 插入记录
    sqlText="insert into cargoalter values ('{}','{}','{}',{},{},'{}','{}');".format(no_str,wai_no,carg_no,num,price,desc,explain)
    cur.execute(sqlText)
    # 提交修改
    conn.commit()

# 删除，不能删除记录

# 查找 根据货物号查找记录
def selCargoAlterByCarg_no(cur,carg_no):
    sqlText = 'select * from cargoalter where calt_carg_no="{}";'.format(carg_no)
    cur.execute(sqlText)
    return cur.fetchall()

# 查找 根据客服员号查找记录
def selCargoAlterByWai_no(cur,wai_no):
    sqlText = 'select * from cargoalter where calt_wai_no="{}";'.format(wai_no)
    cur.execute(sqlText)
    return cur.fetchall()

# 更改，不允许被更改



## Cargo的操作
# 新增货物
def addCargo(conn,cur,wai_no,price,manu,stock,minstock,photo,desc):
    # 获取自增1的记录号
    sqlText = 'select * from cargoinfo;'
    cur.execute(sqlText)
    result = cur.fetchall()
    no = len(result) + 1
    no_str = str(no).zfill(5)
    # 插入货物
    sqlText = "insert into cargoinfo values ('{}','{}','{}',{},{},'{}','{}',0)".format(no_str, price, manu, stock,minstock, photo, desc)
    cur.execute(sqlText)
    conn.commit()
    # 插入相应的记录
    addCargoAlter(conn,cur,wai_no,no_str,stock,price,desc,"新增货物")

# 删除货物
def delcargo(conn,cur,carg_no,wai):
    cargo=selCargoAlterByCarg_no(carg_no)
    return modiCargoDelStatus(conn,cur,cargo,wai)

# 查找，根据货物号查找货物
def selCargoByCarg_no(cur,carg_no):
    sqlText = 'select * from cargoinfo where carg_no="{}";'.format(carg_no)
    cur.execute(sqlText)
    result=cur.fetchone()
    # print(result)
    if result:
        return Cargo(result[0],result[1],result[2],result[3],result[4],result[5],result[6],result[7])
    else:
        return None

# 查找所有货物
def selAllCargo(cur):
    sqlText = 'select * from cargoinfo;'
    cur.execute(sqlText)
    return cur.fetchall()

# 查找所有未被删除的货物
def selAllNotDeleteCargo(cur):
    sqlText = 'select * from cargoinfo where carg_delstatus=0;'
    cur.execute(sqlText)
    return cur.fetchall()

# 更改货物的价格
def modiCargoPrice(conn,cur,cargo,wai_no,price):
    if cargo:
        sqlText = "UPDATE cargoinfo SET carg_price = {} WHERE carg_no = '{}';".format(price, cargo.no)
        cur.execute(sqlText)
        conn.commit()
        addCargoAlter(conn, cur, wai_no, cargo.no, cargo.stocknum, price, cargo.desc, "更改货物价格")
        return 1
    else:
        return 0

# 更改货物的制造厂商
def modiCargoManu(conn,cur,cargo,manu):
    if cargo:
        sqlText = "UPDATE cargoinfo SET carg_manufacturer = {} WHERE carg_no = '{}';".format(manu, cargo.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 更改货物的存储量
def modiCargoStock(conn,cur,cargo,wai_no,stock):
    if cargo:
        sqlText = "UPDATE cargoinfo SET carg_stocknum = {} WHERE carg_no = '{}';".format(stock, cargo.no)
        cur.execute(sqlText)
        conn.commit()
        addCargoAlter(conn, cur, wai_no, cargo.no, stock, cargo.price, cargo.desc, "更改货物存储量")
        return 1
    else:
        return 0

# 更改货物的最小存储量
def modiCargoMinStock(conn,cur,cargo,minstock):
    if cargo:
        sqlText = "UPDATE cargoinfo SET carg_min_stocknum = {} WHERE carg_no = '{}';".format(minstock, cargo.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 更改货物的图片
def modiCargoPhoto(conn,cur,cargo,wai_no,photo):
    if cargo:
        sqlText = "UPDATE cargoinfo SET carg_photo = {} WHERE carg_no = '{}';".format(photo, cargo.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 更改货物的描述
def modiCargoDesc(conn,cur,cargo,wai_no,desc):
    if cargo:
        sqlText = "UPDATE cargoinfo SET carg_desc = {} WHERE carg_no = '{}';".format(desc,cargo.no)
        cur.execute(sqlText)
        conn.commit()
        addCargoAlter(conn, cur, wai_no, cargo.no, cargo.stocknum, cargo.price, desc,"更改货物描述")
        return 1
    else:
        return 0

# 更改货物的删除标识
def modiCargoDelStatus(conn,cur,cargo,wai_no,delstatus):
    if cargo:
        sqlText = "UPDATE cargoinfo SET carg_delStatus = {} WHERE carg_no = '{}';".format(delstatus,cargo.no)
        cur.execute(sqlText)
        conn.commit()
        addCargoAlter(conn,cur,wai_no,cargo.no,cargo.stocknum,cargo.price,cargo.desc,"删除货物")
        return 1
    else:
        return 0

if __name__ == '__main__':
    import ConnDBAndCloseDB
    conn,cur=ConnDBAndCloseDB.connectDB()
    addCargoAlter(conn,cur,'00001','00010',50,50,"这个商品很好")
    ConnDBAndCloseDB.closeDB(cur,conn)
    pass